﻿<%@ Page Language="C#" AutoEventWireup="true" CodeFile="r_comercial_geral.aspx.cs" Inherits="relatorios_r_comercial_geral" %>

<%
    /*string Permissao_RelatoriosFinanceirosComercial = "" + this.Session["Permissao_RelatoriosFinanceirosComercial"];
    if (Permissao_RelatoriosFinanceirosComercial != "1")
    {
        this.Response.Redirect("../login.aspx");
    }*/  
%>

<div style="background-color: White;">
    <%
        double allTotal = 0;
        bool hasResults = false;
        DateTime now = DateTime.Now;
        string month = this.Request["month"];
        string year = this.Request["year"];            
        int iMonth = -1;
        int iYear = -1;            
        int maxDay = 31;
            
        // Month
        if (string.IsNullOrEmpty(month) == true)
        {
            iMonth = now.Month;
        }
        else
        {
            try
            {
                iMonth = int.Parse(month);
            }
            catch { iMonth = now.Month; }
        }
        // Year            
        if (string.IsNullOrEmpty(year) == true)
        {
            iYear = now.Year;
        }
        else
        {
            try
            {
                iYear = int.Parse(year);
            }
            catch { iYear = now.Year; }                
        }

        maxDay = DateTime.DaysInMonth(iYear, iMonth);            
        //DateTime targetDate = DateTime.Now;     
    %>
    <center>
        <form>
            <table>
                <tr>
                    <td>Mês</td>
                    <td>Ano</td>
                    <td></td>
                </tr>

                <tr>
                    <td>
                        <input type="text" id="month" name="month" value="<%= iMonth %>" />
                    </td>
                    <td><input type="text" id="year" name="year" value="<%= iYear %>" /></td>
                    <td><input type="submit" value="ok" /></td>
                </tr>
            </table>
        </form>
    </center>
          
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable([
                ['Dia', 'Atendimentos'],
                <%    
                    allTotal = 0;                                    
                    hasResults = false;
                    PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.BIConnectionString);
                    PL.DataBase.DAO.Instance.Connector.Open();
    
                    string message = "";
                    try
                    {                                 
                        DateTime targetDate = new DateTime(iYear, iMonth, 1);   
                        DateTime maxDate = new DateTime(iYear, iMonth, maxDay);
                        string sql = "";
                        
                        /*
                        sql += "SELECT Vendedor, SUM(Total) As tt From BI_Atendimentos_Comerciais ";
                        sql += " WHERE ";                    
                        sql += " Data >= CONVERT(VARCHAR, '" + targetDate.ToString().Split(' ')[0] + "',103) ";
                        sql += " AND Data <= CONVERT(VARCHAR, '" + maxDate.ToString().Split(' ')[0] + "',103) ";
                        sql += " GROUP BY Vendedor ";
                        */
                        
                        sql += " SELECT        COUNT(l.Codigo) AS TotalAtendimentos, u.Nome ";
                        sql += " FROM            logs AS l INNER JOIN  ";
                        sql += "                    sacuser AS u ON l.Cod_Usuario = u.Código ";
                        sql += " WHERE ";
                        sql += " (l.Funcao = 'Realizou atendimento') ";
                        sql += " AND (l.Data >= CONVERT(DATE, '" + targetDate.ToString().Split(' ')[0] + "',103)) ";                        
                        sql += "  AND (l.Data <= CONVERT(DATE, '" + maxDate.ToString().Split(' ')[0] + "',103)) ";                        
                        sql += " AND (u.Cod_Nivel = 2 OR u.Cod_Nivel = 6) AND (u.Código <> 123)";
                        sql += " GROUP BY u.Nome ";
                        sql += " ORDER BY u.Nome ";
                        
                        var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
                        while (reader.Read() == true)
                        {            
                            hasResults = true;
                            this.Response.Write("['" + reader["Nome"] + "', " + reader["TotalAtendimentos"] + "],");

                                                        try
                            {
                                allTotal += double.Parse("" + reader["TotalAtendimentos"]);
                            } catch {}
                        }
                        reader.Close();
                    }
                    catch (Exception e)
                    {
                        message = e.Message;
                    }

                    PL.DataBase.DAO.Instance.Connector.Close();
                    this.Response.Write(message);
                %>
            ]);

            <%
                if (hasResults == true)
                {
            %>
                    var options = {
                        title: 'Atendimentos no mês'
                    };

                    var chart = new google.visualization.LineChart(document.getElementById('r_comercial_atendimentos'));

                    chart.draw(data, options);
            <%
                }
            %>
        }
    </script>
    <div id="r_comercial_atendimentos" style="width: 80%; height: 200px;">
        <center>Atendimentos no mes: nenhum dado encontrado</center>
    </div>
    <br />
    <br />
    <center>
        Total no periodo: <strong> <%= allTotal.ToString("#,##0")%></strong> atendimentos
    </center>

    <!-- ------- -->

    <br />
    <br />

    <!-- ------- -->
    
    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable([
                ['Colaborador', 'Mensalidade R$'],
                <%
                    allTotal = 0;
                    hasResults = false;
                    PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.BIConnectionString);
                    PL.DataBase.DAO.Instance.Connector.Open();
                        
                    try
                    {   
                        DateTime targetDate = new DateTime(iYear, iMonth, 1);   
                        DateTime maxDate = new DateTime(iYear, iMonth, maxDay);
                                                
                        string sql = "";
                        /*
                        sql += " SELECT        D0S001Vendedor, SUM(F0S203Mensalidade) AS Total ";
                        sql += " FROM            BI_CP_Orcamentos ";
                        sql += " WHERE        ";
                        sql += " D2N009Data >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "' ";                    
                        sql += " AND D2N009Data <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "' ";                    
                        sql += " AND (D2N010Situacao = '3') ";
                        sql += " AND (F0S203Mensalidade IS NOT NULL) ";
                        sql += " GROUP BY D0S001Vendedor";
                        */

                        sql += "SELECT        sistema.Usuario AS Vendedor, SUM(orcamentoitens.Valor * orcamentoitens.Qtde) AS Valor, SUM(orcamentoitens.Mensalidade) AS Mensalidade ";
                        sql += "FROM            orcamento LEFT OUTER JOIN ";
                        sql += "                         product LEFT OUTER JOIN ";
                        sql += "                         orcamentoitens ON product.Código = orcamentoitens.Cod_Produto ON orcamento.Codigo = orcamentoitens.CodOrcamento LEFT OUTER JOIN ";
                        sql += "                         sistema ON orcamento.CodVendedor = sistema.Codigo ";
                        sql += "WHERE        (orcamento.DTFinalizado >= CONVERT(DATETIME, '2011-12-01 00:00:00', 102)) AND (orcamento.CodCliente <> 1399) AND (CAST(orcamento.Data AS DATETIME)  ";
                        sql += "                         >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "') AND (CAST(orcamento.Data AS DATETIME) <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "') AND (orcamento.Situacao = 3)  ";
                        List<int> codes = PL.General.GetCodesToExclude();
                        foreach (var code in codes)
                        {
                            sql += " AND orcamentoitens.Cod_Produto <> " + code;
                        }  
                        sql += " GROUP BY sistema.Usuario, orcamento.Situacao  ";
                        sql += " ORDER BY Vendedor ";

                        var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
                        while (reader.Read() == true)
                        {                                                         
                            string solution = "" + reader["Vendedor"];
                            string total = "" + reader["Mensalidade"];
                            if (string.IsNullOrEmpty(solution) == false && total != "0,00")
                            {
                                hasResults = true;    
                                this.Response.Write("['" + solution + "', " + total.Replace(",", ".") + "],");
                            }

                            try
                            {
                                allTotal += double.Parse(total);
                            } catch {}
                        }
                        reader.Close();                        
                    }
                    catch (Exception e)
                    {
                        message = e.Message;
                    }

                    PL.DataBase.DAO.Instance.Connector.Close();                    
                    this.Response.Write(message);
                %>
            ]);

            <%
                if (hasResults == true)
                {
            %>
                    var options = {
                        title: 'Mensalidades adquiridas no mês por colaborador'
                    };

                    var chart = new google.visualization.LineChart(document.getElementById('r_mensalidade_comercial_por_colaborador'));

                    chart.draw(data, options);
            <%
                }
            %>
        }
    </script>
    <div id="r_mensalidade_comercial_por_colaborador" style="width: 80%; height: 200px;">
        <center>Mensalidade por coloborador: nenhum dado encontrado</center>
    </div>
    <br />
    <br />
    <center>
        Total no periodo: <strong>R$ <%= allTotal.ToString("#,##0.00")%></strong>
    </center>

    <!-- ------- -->

    <br />
    <br />

    <!-- ------- -->

    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);
        function drawChart() {
            var data = google.visualization.arrayToDataTable([
                ['Colaborador', 'Tx R$'],
                <%
                    allTotal = 0;
                    hasResults = false;
                    PL.DataBase.DAO.Instance.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.BIConnectionString);
                    PL.DataBase.DAO.Instance.Connector.Open();
                        
                    try
                    {   
                        DateTime targetDate = new DateTime(iYear, iMonth, 1);   
                        DateTime maxDate = new DateTime(iYear, iMonth, maxDay);
                                                
                        string sql = "";
                        /*
                        sql += " SELECT        D0S001Vendedor, SUM(F0S202Valor) AS Total ";
                        sql += " FROM            BI_CP_Orcamentos ";
                        sql += " WHERE        ";
                        sql += " D2N009Data >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "' ";                    
                        sql += " AND D2N009Data <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "' ";                    
                        sql += " AND (D2N010Situacao = '3') ";
                        sql += " AND (F0S202Valor IS NOT NULL) ";
                        sql += " GROUP BY D0S001Vendedor";
                        */
                        
                        sql += "SELECT        sistema.Usuario AS Vendedor, SUM(orcamentoitens.Valor * orcamentoitens.Qtde) AS Valor, SUM(orcamentoitens.Mensalidade) AS Mensalidade ";
                        sql += "FROM            orcamento LEFT OUTER JOIN ";
                        sql += "                         product LEFT OUTER JOIN ";
                        sql += "                         orcamentoitens ON product.Código = orcamentoitens.Cod_Produto ON orcamento.Codigo = orcamentoitens.CodOrcamento LEFT OUTER JOIN ";
                        sql += "                         sistema ON orcamento.CodVendedor = sistema.Codigo ";
                        sql += "WHERE        (orcamento.DTFinalizado >= CONVERT(DATETIME, '2011-12-01 00:00:00', 102)) AND (orcamento.CodCliente <> 1399) AND (CAST(orcamento.Data AS DATETIME)  ";
                        sql += "                         >= '" + targetDate.Day + "/" + targetDate.Month + "/" + targetDate.Year + "') AND (CAST(orcamento.Data AS DATETIME) <= '" + maxDate.Day + "/" + maxDate.Month + "/" + maxDate.Year + "') AND (orcamento.Situacao = 3)  ";                        
                        List<int> codes = PL.General.GetCodesToExclude();
                        foreach (var code in codes)
                        {
                            sql += " AND orcamentoitens.Cod_Produto <> " + code;
                        }                                                
                        sql += " GROUP BY sistema.Usuario, orcamento.Situacao  ";
                        sql += " ORDER BY Vendedor ";

                        var reader = PL.DataBase.DAO.Instance.Connector.QueryWithReader(sql);
                        while (reader.Read() == true)
                        {                                       
                            string solution = "" + reader["Vendedor"];
                            string total = "" + reader["Valor"];
                            if (string.IsNullOrEmpty(solution) == false && total != "0,00")
                            {
                                hasResults = true;
                                this.Response.Write("['" + solution + "', " + total.Replace(",", ".") + "],");
                            }

                            try
                            {
                                allTotal += double.Parse(total);
                            } catch {}
                        }
                        reader.Close();
                    }
                    catch (Exception e)
                    {
                        message = e.Message;
                    }

                    PL.DataBase.DAO.Instance.Connector.Close();
                    this.Response.Write(message);
                %>
            ]);

            <%
                if (hasResults == true)
                {
            %>
                    var options = {
                        title: 'Tx adquiridas no mês por colaborador'
                    };

                    var chart = new google.visualization.LineChart(document.getElementById('r_tx_comercial_por_colaborador'));

                    chart.draw(data, options);
            <%
                }
            %>
        }
    </script>
    <div id="r_tx_comercial_por_colaborador" style="width: 80%; height: 200px;">
        <center>Tx por colaborador: nenhum dado encontrado</center>
    </div>
    <br />
    <br />
    <center>
        Total no periodo: <strong>R$ <%= allTotal.ToString("#,##0.00")%></strong>
    </center>
</div>